PostgreSQL 中的自增列
Table of Contents
1. 自增列推荐使用 int8 (bigint)
如果表规模较小，int4 够用，但即便使用了 int8，多存储的 4 个字节也不会产生多大影响。
如果表规模较大，int4 的最大值 2147483647 可能不够用。将活跃的大表的主键从 int4 改成 int8 的 SQL 语句很简单，但执行后该表不可用的时间会相当长。
int8 最大值 9223372036854775807，即使每秒使用 10000 个值，也需要 3000 万年才能超过最大值。另，PG 不支持无符号数。
2. sequence 是自增列的底层对象
sequence 是 PG 底层的一个 object，它起自增计数器的作用，可以创建多个。
sequence 被并发访问时不会产生两次同样的数值。如果担心高并发导致访问 sequence 出现瓶颈，可以针对 sequence 指定 Cache，它使 sequence 预先生成数值并保存在内存中，让取数速度更快。
sequence 与事务无关，即使事务产生回滚，sequence 也不会重置计数。这样不但能保证高性能，也不会带来问题。如希望 sequence 的值单调连续，那就不能用 sequence，要涉及效率更低且更复杂的方案。
sequence 对象可以被直接使用，或者通过 serial/bigserial/IDENTITY 被间接使用。
3. 用 bigserial 类型还是 IDENTITY 列
PG 11 引入了 IDENTITY 约束，相比 bigserial 有两个优势：
- IDENTITY 列在 SQL 标准中，bigserial 是 PG 专有类型，迁移数据库时工作量小一些
- 插入数据时，如果插入数据时明确在 IDENTITY 列上指定了一个值，PG 会报错，这避免了手动输入的值和此后生成的值发生冲突的问题
除非使用的是 PG 10 或更低版本，否则不推荐 bigserial。
4. 如何创建
4.1. 先创建 sequence 对象实例再设定列的默认值最后二者关联
CREATE SEQUENCE use_serial_id_seq;
CREATE TABLE use_serial (
id int8 DEFAULT nextval('use_serial_id_seq') PRIMARY KEY,
-- ...
);
ALTER SEQUENCE use_serial_id_seq OWNED BY some_table.id;
-- OWNED BY 将 use_serial_id_seq 这个 sequence 和 user_serial 表的 id 列绑定
-- DROP user_serial 表的 id 列，那么 use_serial_id_seq 也会被 drop
4.2. 先建表再创建 sequence 对象实例最后关联对象实例到列
CREATE TABLE use_serial (
id int8 PRIMARY KEY,
-- ...
);
CREATE SEQUENCE use_serial_id_seq OWNED BY use_serial.id;
ALTER TABLE use_serial ALTER id SET DEFAULT nextval('use_serial_id_seq');
它与前一节结果相同。
4.3. 直接使用 serial 或 bigserial 作为列的类型
-- serial 类型的基础类型是 int4
-- bigserial 类型的基础类型是 int8
CREATE TABLE use_serial (
id bigserial PRIMARY KEY,
-- ...
);
它是前两节的命令的简化版。数据库会自动创建 sequence 对象实例，并将对象实例绑定到表中的列。
4.4. 使用 IDENTITY 约束
-- 用户不能指定 id 的值
CREATE TABLE some_table (
id int8 GRANTED ALWAYS AS IDENTITY PRIMARY KEY,
-- ...
)
-- 用户可以指定 id 的值
CREATE TABLE some_table (
id int8 GRANTED BY DEFAULT AS IDENTITY PRIMARY KEY,
-- ...
)
PG 会隐式地创建一个 sequence 对象实例绑定在指定的列上。
对于不能指定 identity 值的列来说，可以使用 INSERT INTO <table-name> (...)
OVERRIDING SYSTEM VALUE VALUES(...); 来强制指定 identity 的值，但这可能导致后续生成的 identity 列的值与被插入的值冲突。比如，强制插入了 id 为 4 的数据，而插入前表中 id 列的最大值为 3，那么再插入一条新数据时，这条新数据会取到 id 为 4，和表中已有数据冲突。
4.5. 其他思路
使用 BEFORE INSERT 触发器。生成的值会直接覆盖要被插入的值，并且性能下降严重。